Cube View Extender: Advanced Cube View Formatting

To apply advanced formatting to Cube Views, users can apply a Cube View Extender Business Rule.  See Business Rules in Application Tools for more information.  A custom formatting formula built inside the Cube View can also be used.  Using custom formatting allows the Cube View design to go beyond the standard Cube View formatting properties and provides flexibility for specific formatting needs.  See the OneStream API Overview Guide as well as the OneStream API Details and Database Documentation Guide for more details on how this Business Rule is used.

Cube View Extender Business Rule Structure

When creating a new Cube View Extender Business Rule or Formula within a Cube View, some example logic is presented in order to have a starting point rather than starting the rule from scratch.  This lays out a general structure for what can be accomplished within a custom rule like this, but the logic is commented out initially. There are Cube View Extender Snippets available for download from the MarketPlace Store under the Snippet Editor Solution.  These will also help with a starting point for common uses.

Below is an example of a Cube View Extender Business Rule for review and understanding:

See the OneStream API Overview Guide for more details on this Business Rule including the various lists of potential options available for setting properties. Search in the below highlighted section of the API Guide:

The breakdown of each section for this type of rule are below:

Args.FunctionType

Select Case args.FunctionType is the expression used when a certain process needs to be isolated and run special logic.  The Case statement in the example Business Rule above is necessary to determine one of two operations that are being performed to generate the Cube View Report.  These are GetReportOptions or FormatReportUIItem.

CVExtenderFunctionType.GetReportOptions

This is for the retrieval of and setting of properties such as margins and height of title and footer.  A value of -1 means to use the default value and is not necessary if that property is not being overridden. Any of these numeric settings is represented in pixels, roughly the width of a human hair, so these are precise measurements.

Select Case args.FunctionType

Case Is = CVExtenderFunctionType.GetReportOptions

Dim reportOptions As New CVExtenderReportOptions()

reportOptions.ReportMarginTop = 100

reportOptions.ReportMarginBottom = -1

reportOptions.PageHeaderTitlesHeight = 20

reportOptions.PageFooterHeight = -1

Return reportOptions

Notice in the rule above a new CVExtenderReportOptions object is declared so that a few properties can be set for that object and then written back with the Return statement at the bottom of the section. Without the Return statement, the properties do not get set for this particular instance when this report is run. 

These property settings will not override the saved properties for this Cube View, but just override at run time. Properties that can be set in a CVExtenderReportOptions object:

  • PageFooterHeight

  • PageHeaderTitlesHeight  

  • ReportMarginBottom  

  • ReportMarginTop

CVExtenderFunctionType.FormatReportUIItem

The key concept to grasp for how these Cube View Extender Business Rules apply to the formatting of a report is that every item is looped through and is eligible to have its format changed through logic. When a report is run, it plots every label, every line, everything seen on the page one by one. Based on the current item being processed (i.e. Args.Report.CurrentUIItem), the system has the context of many properties (which depend on the type of item it is, or UIItemType) and the Cube View Column or Row to which it is related.

This is for formatting a specific Report User Interface Item, which could be a data cell, line, footer, header, row header, column header or another item on the report. These object types are called a UIItemType.

Case Is = CVExtenderFunctionType.FormatReportUIItem

                 Dim uiItem As CVExtenderReportUIItem = args.Report.CurrentUIItem

                If uiItem.UIItemType = XFReportUIItemType.DataCellLabel Then…

Based on the UIItemType, apply logic that will set properties as appropriate and based on conditions.

Args.Report

Can set properties in the report view of the Cube View output.  See below for some examples.

  • CurrentUIItem
    Example: Dim uiItem As CVExtenderReportUIItem = args.Report.CurrentUIItem
    This is the key Report Arg as it returns an object called CVExtenderReportUIItem

    • A CVExtenderReportUIItem can return:

      • UIItemType
        This a key property. This will reveal if the Report Item is a Label, Page Header or Footer Label, Row or Column Header Label or a Data Cell Label (meaning an amount or cell on the report). Once it knows what type of Report Item is being analyzed, certain properties can be applied.
        Example of this logic:
        If uiItem.UIItemType = XFReportUIItemType.DataCellLabel Then…

        • Whether the item has data (uiItem.XFHasData) if the type is DataCellLabel

        • The amount of the DataCellLabel (uiItem.XFAmount)

        • Text stored and how formatted (uiItem.Text, uiItem.FontFamily or uiItem.FontSize)

        • The item’s Name (uiItem.Name)

        • Change the colors (uiItem.TextColor, uiItem.BackgroundColor, uiItem.BorderColor, etc.) with a statement such as
          uiItem.BackgroundColor = XFColors.Yellow

        • Change borders and lines (uiItem.BorderSides, uiItem.BorderLineStyle, etc.)

        • Whether the item can grow or shrink based on content, if it needs to stay on one row, or how large that row or column should be (uiItem.CanGrow, uiItem.CanShrink, uiItem.Padding or uiItem.WordWrap)

  • Margin sizes

    • api.Report.MarginLeft

    • api.Report.MarginRight

    • api.Report.PageWidthMinusMargins

  • Page position and margin width
    api.Report.CurrentPageInfo controls the left/right/center positioning as well as page/header width

  • Auto Fit settings

    • api.Report.AutoFitToPageWidth

    • api.Report.AutoFitNumPagesWide

Args.CubeView

These Args can be used to retrieve properties from a Cube View being processed when the report is run, but really are here more for internal use. Some of these properties could be used as conditions, when setting labels or other properties. Examples are:

  • Paper Size

  • Margins

  • Titles

  • Headers & Footers

  • Cube View POV settings

  • Row & Column Height & Width

Args.PageInstanceInfo

This is for setting Dashboard Page State and is not related to Cube View Extenders.

Args.CustomSubstVars

This is not related to Cube View Extenders for the most part.  They can provide the ability to retrieve the name value pairs of a custom Parameter applied when this Cube View was run and the choice the user made. For example, if the user selects an Entity upon running the report, MyEntity = Houston could be returned and used in a custom report, however, there are other methods to apply this same information.

Manipulating Formats based on the related Cube View Row or Column

When custom formatting should only impact specific Cube View Report Rows or Columns related to the CurrentUIItem being processed, this type of statement can be used to first declare a new row or column object.  It can then apply formatting based on the properties retrieved from this part of the Cube View related to that item, such as a Row Name. 
Example:
        If uiItem.UIItemType = XFReportUIItemType.DataCellLabel Then
                Dim cvRow As CubeViewRow = uiItem.GetCubeViewRow
                Dim cvCol As CubeViewCol = uiItem.GetCubeViewColumn

As shown in the previous example, a test can be performed to see if there is anything to be rendered in this column or row with If Not cvRow Is Nothing Then, which is a good practice.

Conditional logic can be used before applying formatting to labels or data cells such as the Name of the Cube View Row related to the CurrentUIItem:
        If cvRow.Name.XFContainsIgnoreCase("Total") Then…